﻿<?php

/**
 * @version        $Id: index.php 20806 2011-02-21 19:44:59Z dextercowley $
 * @package        Joomla.Site
 * @copyright    Copyright (C) 2005 - 2011 Open Source Matters, Inc. All rights reserved.
 * @license        GNU General Public License version 2 or later; see LICENSE.txt
 */                 
// Set flag that this is a parent file.
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);
/** PHPExcel_IOFactory */  

require_once JPATH_BASE.DS.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';
require_once JPATH_BASE.DS.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel'.DS.'IOFactory.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();

$objPageSetup = new PHPExcel_Worksheet_PageSetup();
$objPageSetup->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPageSetup->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$activeSheet->setPageSetup($objPageSetup);
$activeSheet->getPageSetup()->setHorizontalCentered(true);

$pageMargins = $activeSheet->getPageMargins();
$pageMargins->setTop(0);
$pageMargins->setBottom(0);
$pageMargins->setLeft(0);
$pageMargins->setRight(0);
 
$gdImage = imagecreatefromjpeg($_SERVER['DOCUMENT_ROOT'].JURI::root(true).'/images/exel_vip_logo.jpg');
$objDrawing = new PHPExcel_Worksheet_MemoryDrawing();
$objDrawing->setName('WMCVIP logo');
$objDrawing->setDescription('WMCVIP logo');
$objDrawing->setImageResource($gdImage);
$objDrawing->setRenderingFunction(PHPExcel_Worksheet_MemoryDrawing::RENDERING_JPEG);
$objDrawing->setMimeType(PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);

$objDrawing->setHeight(100);   
$objDrawing->setCoordinates('A1');    
$objDrawing->setOffsetX(10); 
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$db = JFactory::getDbo();

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');

foreach($cols as $item)
{	
	if($item == 'A'){
		setBackgroundColor($activeSheet, $item, 5, 14);
	}
	if($item == 'B'){
		setBackgroundColor($activeSheet, $item, 5, 11.29);
	}
	if($item == 'C'){
		setBackgroundColor($activeSheet, $item, 5, 35);
	}
	if($item == 'D'){
		setBackgroundColor($activeSheet, $item, 5, 7.29);
	}
	if($item == 'E'){
		setBackgroundColor($activeSheet, $item, 5, 15.43);
	}
	if($item == 'F'){
		setBackgroundColor($activeSheet, $item, 5, 9.71);
	}
	if($item == 'G'){
		setBackgroundColor($activeSheet, $item, 5, 10.71);
	}
	else if($item == 'H'){
		setBackgroundColor($activeSheet, $item, 5, 25.43);
	} 		 
}

$activeSheet->getColumnDimension('A')->setWidth(15.3);
$activeSheet->getColumnDimension('B')->setWidth(12);
$activeSheet->getColumnDimension('C')->setWidth(30.5);
$activeSheet->getColumnDimension('D')->setWidth(8);
$activeSheet->getColumnDimension('E')->setWidth(16.2);
$activeSheet->getColumnDimension('F')->setWidth(10.5);
$activeSheet->getColumnDimension('G')->setWidth(14.5);
$activeSheet->getColumnDimension('H')->setWidth(28.5);

$fill = $activeSheet->getStyle("B14:H14")->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('CCCCCC');

$activeSheet->setCellValue('G2', 'WMC VIP');
$activeSheet->getStyle('G2'.$index)->getFont()->setBold(true);
$activeSheet->setCellValue('G3', '193 Tran Hung Dao, District 1, Vietnam');

$activeSheet->setCellValue('F4', 'Phone:');
$activeSheet->setCellValue('F5', 'Email:');
$activeSheet->setCellValue('F6', 'Website:');

$activeSheet->setCellValue('G4', '39206515 ext 6070');
$activeSheet->setCellValue('G5', 'info@wmcvip.vn');
$activeSheet->setCellValue('G6', 'http://wmcvip.vn');

$activeSheet->setCellValue('A7', 'Purchase Order No. 11');
$activeSheet->getStyle('A7')->getFont()->setBold(true);
$activeSheet->setCellValue('A9', 'Member Name:');
$activeSheet->setCellValue('A10', 'Member No:');
$activeSheet->setCellValue('A11', 'Phone No:');
$activeSheet->setCellValue('A12', 'Address:');
$activeSheet->setCellValue('A13', 'Redemption Code:');
$activeSheet->getStyle('A9:A13'.$index)->getFont()->setBold(true);

$activeSheet->setCellValue('B9', $this->user->l_name.' '.$this->user->f_name);
$activeSheet->setCellValue('B10', $this->user->member_id);
$activeSheet->setCellValueExplicit('B11', $this->user->phone, PHPExcel_Cell_DataType::TYPE_STRING);
$activeSheet->setCellValue('B12', $this->user->address);
$activeSheet->setCellValue('B13', $this->models[0]->code);

$activeSheet->setCellValue('E9', 'Outlet pick up:');
$activeSheet->setCellValue('E10', 'Requested Outlet:');
$activeSheet->setCellValue('E11', 'Created date');
$activeSheet->setCellValue('E12', 'Pick Up Date:');
$activeSheet->setCellValue('E13', 'Status:');
$activeSheet->getStyle('E9:E13'.$index)->getFont()->setBold(true);

$activeSheet->setCellValue('G9', $this->models[0]->outlet);
$activeSheet->setCellValue('G10', $this->models[0]->requested_outlet_name);
$activeSheet->setCellValue('G11', strtotime($this->models[0]->exchange_date) ? date( 'd-m-Y',strtotime($this->models[0]->exchange_date)) : '');
$activeSheet->setCellValue('G12', strtotime($this->models[0]->pick_up_date)? date( 'd-m-Y', strtotime($this->models[0]->pick_up_date)) : '');

if($this->models[0]->approved == 1) : 
	$status = 'Approve';
elseif($this->models[0]->approved == 0) :
	$status = 'Waiting';
elseif($this->models[0]->approved == 2) :
	$status = 'UnApproved';
elseif($this->models[0]->approved == 9) :
	$status = 'Deleted by us';	
endif;

$activeSheet->setCellValue('G13', $status);

$activeSheet->getStyle('A14:H14')->getFont()->setBold(true);
$activeSheet->setCellValue('A14', 'Products:');
$activeSheet->setCellValue('B14', 'Product Name');
//$activeSheet->setCellValue('C14', 'Product Name');
$activeSheet->setCellValue('D14', 'Quantity');
$activeSheet->setCellValue('E14', 'Voucher Serial No.');
$activeSheet->setCellValue('F14', 'Expired');
$activeSheet->setCellValue('G14', 'Unit Price');
$activeSheet->setCellValue('H14', 'Type Redemption');
$activeSheet->getStyle('F2:F6')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle('E5:F5')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle('B10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle('B11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$activeSheet->getStyle('B14:H14')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//$activeSheet->getStyle('A1')->getFont()->setSize(24);
//$activeSheet->getStyle('A1')->getFont()->setBold(true);

// End init excel
//echo '<pre>';print_r($this->models);exit;
$index = 15;
$size = count($this->models);
$totalPoint = 0;
$totalCash = 0;

$tmpcount = 1;
$tmp_tr = array();
foreach ($this->tr as $key => $row) {
	$tmp_tr[$row->poulet][data][] = $row;
	$tmp_tr[$row->poulet][total] = abs($row->point) + $tmp_tr[$row->poulet][total];
	$tmp_tr[$row->poulet][outlet] = $row->name;
	$tmp_tr[$row->poulet][outletid] = $row->poulet;
}
$countdata = count($tmp_tr);

if(($size < 11) && ($countdata >= 4)){
	
	if( (11-$size) > ($countdata - 4) ){
		
		$row_empty = ceil((15- $size - $countdata)/2);
		
		$size += $row_empty;
	}
	
}
else if(($size  < 11) && ($countdata < 4)){

	$size = 11; $row_empty  = 5 - $countdata;
	
}
else if( ($size >= 11) && ( ($size + $countdata) < 15)){
	
	$row_empty = 16 - $size - $countdata;
	
	
}

for($i=0; $i<$size; $i++)
{
	$product_arr = unserialize($this->models[$i]->product);
	$product = $product_arr['en-GB'];
	//$activeSheet->setCellValue('B'.$index,$this->models[$i]->p_code);
	$activeSheet->mergeCells("B$index:C$index");
	$activeSheet->setCellValue('B'.$index,$product);
	$activeSheet->getColumnDimension('B'.$index)->setWidth(43.5);
	$activeSheet->setCellValue('D'.$index,$this->models[$i]->quantity);            
	$activeSheet->setCellValue('E'.$index,$this->models[$i]->code_voucher);   
	$activeSheet->setCellValue('F'.$index,!strtotime($this->models[$i]->expired) ? '' : date("d-m-Y",strtotime($this->models[$i]->expired)) );            
	$activeSheet->getStyle('F'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	
	$activeSheet->setCellValue('G'.$index,$this->models[$i]->price); 
	$objPHPExcel->getActiveSheet()->getStyle('G'.$index)->getNumberFormat()->setFormatCode("#,##0");	
	
	$activeSheet->setCellValue('H'.$index,($this->models[$i]->cash > 0 ? 'Point: '.number_format($this->models[$i]->point,0,".",",").'. Cash: '.number_format($this->models[$i]->cash, 2, '.', ',') : ($this->models[$i]->point > 0 ? 'Point: '.number_format($this->models[$i]->point,0,".",",") : "")));
	
	$index++;
	$totalPoint += $this->models[$i]->point;
	$totalCash += $this->models[$i]->cash;
}
$num = $index-1;
$activeSheet->getStyle("A14")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$activeSheet->mergeCells("E9:F9");
$activeSheet->mergeCells("E10:F10");
$activeSheet->mergeCells("E11:F11");
$activeSheet->mergeCells("E12:F12");
$activeSheet->mergeCells("E13:F13");
$activeSheet->mergeCells("G11:H11");$activeSheet->mergeCells("G12:H12");$activeSheet->mergeCells("G13:H13");

$activeSheet->mergeCells("B14:C14");
$activeSheet->mergeCells("B9:D9");
$activeSheet->mergeCells("B10:D10");
$activeSheet->mergeCells("B11:D11");
$activeSheet->mergeCells("B12:D12");
$activeSheet->mergeCells("B13:D13");

$activeSheet->mergeCells("A14:A$num");

//echo $index;exit;
$activeSheet->getStyle("B$index:H$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$fill = $activeSheet->getStyle("B$index:H$index")->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('CCCCCC');
$activeSheet->getStyle("A$index:H$index")->getFont()->setBold(true);
$activeSheet->mergeCells("C$index:F$index");
$activeSheet->setCellValue('A'.$index, 'Transaction:');
$activeSheet->setCellValue('B'.$index, 'ID');
$activeSheet->setCellValue('C'.$index, 'Outlet');
$activeSheet->setCellValue('G'.$index, 'Used Point');
$activeSheet->setCellValue('H'.$index, 'Percentage');

//$countdata = count($this->tr);
//echo "<pre>";print_r($this->tr);exit;
//if($countdata < 7) $countdata = 7;
//$num = $index + $countdata;

//annechau

//if($countdata < 4) $countdata = 4;
$num = $index + $countdata;
if(isset($row_empty) && ($row_empty > 1)){
	$num = $num + $row_empty - 1;
}
$activeSheet->mergeCells("A$index:A$num");
$activeSheet->getStyle("A$index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

//for($i=0;$i<$countdata;$i++){
foreach($tmp_tr as $rav => $data):

	$index++;
	$activeSheet->setCellValue('B'.$index, $tmpcount++);	
	$activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	$outletName = $data[outlet];
	$activeSheet->setCellValue('C'.$index, $outletName);
	$activeSheet->setCellValue('G'.$index, $data[total]);
	$objPHPExcel->getActiveSheet()->getStyle('G'.$index)->getNumberFormat()->setFormatCode("#,##0");
	$activeSheet->setCellValue('H'.$index, number_format(($data[total]/$totalPoint)*100,2, '.', ',').' %');
	$activeSheet->getStyle('H'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	$activeSheet->mergeCells("C$index:F$index");

endforeach;

if(isset($row_empty) && ($row_empty > 1)){

	for($i=1; $i<$row_empty; $i++):
		
		$index++;
		$activeSheet->setCellValue('B'.$index, '');	
		$activeSheet->getStyle('B'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$activeSheet->mergeCells("C$index:F$index");
	
	endfor;
	
}

$index+=1;
$activeSheet->getStyle('A'.$index)->getFont()->setBold(true);
$activeSheet->mergeCells("B$index:H$index");

$activeSheet->getStyle('A'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->setCellValue('A'.$index, 'Total:');

$activeSheet->setCellValue('B'.$index, ($totalPoint > 0 ? 'Point: '.number_format($totalPoint,0,".",",") : '').($totalCash > 0 ? ' Cash: '.number_format($totalCash, 2, '.', ',') : ''));
$activeSheet->getStyle("A$index:B$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);


foreach($cols as $item)
{
	for($i=9;$i<=$index;$i++){
	
		$activeSheet->getStyle($item.$i)->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN)->setColor( new PHPExcel_Style_Color('000000') );
	}
}

//----------------------------------------------
// Trang excel in kho giay A4 nam ngang
// Co tong cong 39 dong 1 trang
//----------------------------------------------

// Tinh so trang hien tai 
$currentPage = ceil($index/ 39);

//Neu so trang hien tai cong them 8 hang chu ky, ma van nam gon trong trang hien tai, thi 8 chu ky hien thi ngay sau do
if ($index + 8 <= $currentPage*39) {
	$index += 2;
// Neu cong them 8 ma lo trang hien tai, thi mang 8 dong chu ky wa trang sau
}  else {	
	$index = ($currentPage)*39+3;
}

// Hien thi chu ky
$activeSheet->setCellValue('A'.$index, 'Prepared by');
$activeSheet->setCellValue('C'.$index, 'Checked by');
$activeSheet->setCellValue('F'.$index, 'Verified by');
$activeSheet->setCellValue('H'.$index, 'Approved by');
$activeSheet->getStyle("A$index:H$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$index+=1;
$activeSheet->setCellValue('A'.$index, '(MS/Admin)');
$activeSheet->setCellValue('C'.$index, '(Loyalty Program Manager/ Director)');
$activeSheet->setCellValue('F'.$index, '(Accounting)');
$activeSheet->setCellValue('H'.$index, '(Mr. Tony)');
$activeSheet->getStyle("A$index:H$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle("A1:H$index")->getFont()->setSize(10);


$fileName = 'redemption_'.date('m_d_Y_H_i_s');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'transactions'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();

flush();
readfile( $fullPath );
exit;          
function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{      
    //$activeSheet->getColumnDimension($colName)->setWidth($width);       
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(10);
    //$style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
